#!/usr/bin/python
import sys
import MySQLdb
import math

conn = MySQLdb.connect (host = "giza",
                           user = "visquick_rw",
                           passwd = "r34dwr1t3",
                           db = "tcga")
cursor = conn.cursor ()
#update coad_read_features set patient_values = '.1:.2:.3', patient_values_mean = .234 where alias = 'N:METH:cg00000292:chr16:28797601::'
args = sys.argv
table = "coad_read_features"
infile = ''
label = ''
op = 'insert'
pass1 = 1
if (len(args) >= 3):
        table = args[1]
	infile = args[2]
	if (len(args) >= 4):
		label = args[3]
		op = args[4]
		if (op == 'update'):
			pass1 = 0	
else:
	print ("usage is py2.6 parse_features.py tablename infile label[mirna0324] op[insert/update]\nrun insert first and scp output to database server and run bash/mysql on tsv, then update will update the patient and expression values,\nbefore running insert/update,feature and patient tables need to be created first. \nparse_features.py requires py2.6")

fin = open(infile,'r')
fout = open('features' + label + '.tsv','w')
fshout = open('features' + label + '.sh','w')
lines = fin.readlines()
fin.close()

def execCradSql(updateSql):
        cursor.execute(updateSql)
        print "sql %s updated: %d" % (updateSql, cursor.rowcount)

logged = 0
linecount = 0
barcodeStr = ""
for line in lines:
	fields=line.strip().split('\t')
	if (linecount == 0):
		patientcount = 0
		for barcode in fields:
			if (barcode.startswith("TCGA")):
				barcodeStr = barcodeStr + barcode + ":"
		if (pass1 == 1):
			print "Insert patient values"
			execCradSql("insert into coad_read_patients_" + label + " values ('" + barcodeStr[0:len(barcodeStr)-1] + "')")
	else:				
		data = fields[0].split(':')
		f = 0
		valuesArray = []
		mean = -1
		valueStr = ""
		logme = 0
		for val in fields:
			if (f != 0):
				sval = val.strip()
				if (logme and float(sval) > 0):
					logged = logged + 1
					valuesArray.append(math.log(float(sval),2))
				else:
					if (sval != 'NA'):
						valuesArray.append(float(sval))
				valueStr =  valueStr + sval + ":"
			else:
				if (val.startswith("N:GEXP:hsa")):
					logme = 1
				else:
					logme = 0
			f = f + 1
		mean = sum(valuesArray)/len(valuesArray)
		if len(data) > 4 and len(data[3]) > 3:
			data[3] = data[3][3:]		
		lineout =  fields[0] + "\t" + "\t".join(data)  + "\n"
		#N:GEXP:hsa*, math.log(val,base)
		if (pass1 == 0):
			execCradSql("update " + table  + " set patient_values = '" + valueStr.rstrip(':') + "', patient_values_mean=" + str(mean) + " where alias='" + fields[0] + "'")
		else:
			fout.write(lineout)
	linecount = linecount + 1
fshout.write("#!/bin/bash\n")
fshout.write("mysql --user=root --password=g1Z@ --database=tcga<<EOFMYSQL\n")
fshout.write("load data local infile '/local/tcga/crad/" + 'features' + label + ".tsv' replace INTO TABLE " + table + " fields terminated by '\\t' LINES TERMINATED BY '\\n';")
fshout.write("\nEOFMYSQL")
print "error logged " + str(logged) + " records"
fout.close()
fshout.close()
